﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace Paging
{
    public class SqlFilter<TEntity> : ISqlFilter<TEntity> where TEntity : class
    {
        public int PageIndex { get;  set; }

        public int PageSize { get;  set; }
        public string BuildSqlByModel(string assign = "")
        {
            string sql = "";
            if (!string.IsNullOrEmpty(assign))
                assign = assign + ".";
            PropertyInfo[] propertyInfos = this.GetType().GetProperties();
            foreach (var property in propertyInfos)
            {
                string fieldName = property.Name;
                if (fieldName.ToLower().Equals("pagesize"))
                    continue;
                if (fieldName.ToLower().Equals("pageindex"))
                    continue;
                object fieldValue = property.GetValue(this);
                if (fieldValue != null)
                {
                    if (string.IsNullOrEmpty(fieldValue.ToString()))
                        continue;
                    if (fieldValue.GetType() == typeof(String))
                    {
                        sql += $" {fieldName} like @{fieldName} and";
                        property.SetValue(this, "%" + fieldValue.ToString() + "%");
                    }

                    else if (fieldValue.GetType() == typeof(Int32) && Convert.ToInt32(fieldValue) == -100) //-100代表全部
                        continue;
                    else if (fieldValue.GetType() == typeof(DateTime) && fieldName.Contains("_"))
                    {
                        string actualFieldName = fieldName.Split('_')[1];
                        if (fieldName.StartsWith("Start_"))
                        {
                            sql += $" {actualFieldName} >= @{fieldName} and";
                        }
                        else if (fieldName.StartsWith("End_"))
                        {
                            sql += $" {actualFieldName} <= @{fieldName} and";
                        }
                    }
                    else
                        sql += $" {fieldName}=@{fieldName} and";
                }
            }
            if (sql.LastIndexOf("and") > -1)
                sql = sql.Substring(0, sql.LastIndexOf("and"));
            if (string.IsNullOrEmpty(sql))
                sql += " 1=1";
            return sql;
        }
        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <typeparam name="T">主表</typeparam>
        /// <param name="selectsql">生成的sql</param>
        /// <param name="assignFields">除主表外其他过滤的表和对应的字段</param>
        /// <returns></returns>
        public async Task<PaginatedItemsViewModel<TEntity>> PagingFilterAsync(SqlConnection connection, 
            string selectsql,
            string[] orderByFields,
            Dictionary<string,string[]> assignFields = null)
        {
            string sql = "";
            PropertyInfo[] propertyInfos = this.GetType().GetProperties();
            foreach (var property in propertyInfos)
            {
                string assignTable = "";
                string fieldName = property.Name;
                if(assignFields != null)
                {
                    foreach (var item in assignFields)
                    {
                        for (int i = 0; i < item.Value.Length; i++)
                        {
                            if (item.Value[i].ToLower().Equals(fieldName.ToLower()))
                            {
                                assignTable = $"{item.Key}.";
                                break;
                            }
                        }
                        if (!string.IsNullOrEmpty(assignTable))
                            break;
                    }
                }
                if (fieldName.ToLower().Equals("pagesize"))
                    continue;
                if (fieldName.ToLower().Equals("pageindex"))
                    continue;
                object fieldValue = property.GetValue(this);
                if (fieldValue != null)
                {
                    if (string.IsNullOrEmpty(fieldValue.ToString()))
                        continue;
                    if (fieldValue.GetType() == typeof(String))
                    {
                        sql += $" {assignTable}{fieldName} like @{fieldName} and";
                        property.SetValue(this, "%" + fieldValue.ToString() + "%");
                    }
                    else if (fieldValue.GetType() == typeof(Int32) && Convert.ToInt32(fieldValue) == -100) //-100代表全部
                        continue;
                    else if (fieldValue.GetType() == typeof(DateTime) && fieldName.Contains("_"))
                    {
                        string actualFieldName = fieldName.Split('_')[1];
                        if (fieldName.StartsWith("Start_"))
                        {
                            sql += $" {assignTable}{actualFieldName} >= @{fieldName} and";
                        }
                        else if (fieldName.StartsWith("End_"))
                        {
                            sql += $" {assignTable}{actualFieldName} <= @{fieldName} and";
                        }
                    }
                    else
                        sql += $" {assignTable}{fieldName}=@{fieldName} and";
                }
            }
            if (!string.IsNullOrEmpty(sql))
                sql = " where " + sql;
            if (sql.LastIndexOf("and") > -1)
                sql = sql.Substring(0, sql.LastIndexOf("and"));
            string executeSql = selectsql + sql;
            if (orderByFields.Length > 0)
            {
                executeSql += " order by ";
                for (int i = 0;i< orderByFields.Length; i++)
                {
                    executeSql += $"{orderByFields[i]} ";
                }
            }
            long count = await connection.QuerySingleOrDefaultAsync<long>(executeSql, this);
            var data = await connection.QueryAsync<TEntity>(
                     executeSql + @" offset ((@PageIndex-1)*@PageSize) rows
                     fetch next @PageSize rows only"
                     , this);

            return new PaginatedItemsViewModel<TEntity>(this.PageIndex, this.PageSize, count, data);
        }
    }
}
